import mysql.connector
import csv
temp=[]
def load_data(file):
    print('读取data文件...')
    try:
        file_open = open(file, 'r')
        reader = csv.reader(file_open)
    except:
        print('data文件不存在')
    for line in reader:
        temp.append(line)
    return temp

def merge_sqlcreate(array):
    tempsql='('
    for i in array:
        tempsql+=i
        tempsql +=' varchar(30),'
    tempsql =tempsql[:-1]+ ')'
    print(tempsql)
    return tempsql

def merge_sql(array):
    tempsql='('
    for i in array:
        tempsql+='"{}"'.format(i)
        tempsql +=','
    tempsql = tempsql[:-1] + ')'
    return tempsql

def write_mysql(temp):
    conn = mysql.connector.connect(host = '120.79.184.105',user='root', password='root', database='DjangoWeb', use_unicode=True)
    cursor = conn.cursor()
    cursor.execute('create table course {}'.format(merge_sqlcreate(temp[0])))
    for i in temp:
        if i==temp[0]:
            continue
        cursor.execute('insert into course values {}'.format(merge_sql(i)))
        print('{}'.format(merge_sql(i)))
    # cursor.execute('alter table defect add column id int(10) auto_increment primary key')
    conn.commit()
    conn.close

write_mysql(load_data('course.csv'))